using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Xml;
using System.Text;

namespace System.Web.Core
{
    public class SqlEntityProvider : EntityProvider
    {
        #region Private Members

        private string connectionString;

        #endregion

        #region Override Methods

        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }
            base.Initialize(name, config);

            string connectionStringName = config["connectionStringName"];
            if (connectionStringName == null || connectionStringName.Length < 1)
            {
                throw new ProviderException("Connection name not specified.");
            }
            connectionString = ConfigurationManager.AppSettings[connectionStringName];
            if (connectionString == null || connectionString.Length < 1)
            {
                throw new ProviderException("Connection string not found.");
            }
        }

        public override Entity GetEntity(BaseRequestBinder requestBinder)
        {
            Entity entity = null;

            EntityRequest request = requestBinder.Request as EntityRequest;

            using (SqlConnection connection = GetConnection())
            {
                SqlCommand sqlCommand = null;

                if (request != null && !string.IsNullOrEmpty(request.CommandIdent))
                {
                    Command command = Configuration.Instance.GetCommand(request.CommandIdent);
                    sqlCommand = GetSqlCommand(connection, command, request);

                    IDataReader reader = sqlCommand.ExecuteReader();

                    Entity tempEntity = null;
                    EntityList currentEntityList = null;
                    EntityList childEntityList = new EntityList();
                    foreach (ReturnEntity returnEntity in command.ReturnEntities)
                    {
                        if (returnEntity.EntityReturnMode == EntityReturnMode.Single)
                        {
                            if (reader.Read())
                            {
                                tempEntity = PopulateEntity(returnEntity.EntityType, reader, returnEntity);
                                if (request.Entity.GetType().IsAssignableFrom(returnEntity.EntityType))
                                {
                                    entity = tempEntity;
                                }
                                else
                                {
                                    childEntityList.Add(tempEntity);
                                }
                            }
                        }
                        else if (returnEntity.EntityReturnMode == EntityReturnMode.Multiple)
                        {
                            currentEntityList = new EntityList();
                            while (reader.Read())
                            {
                                currentEntityList.Add(PopulateEntity(returnEntity.EntityType, reader, returnEntity));
                            }
                            childEntityList.Add(currentEntityList);
                        }
                        reader.NextResult();
                    }

                    if (entity != null)
                    {
                        entity.EntityList = childEntityList;
                    }

                    reader.Close();
                    CloseConnection(connection);
                }
                else
                {
                    if (request.ReturnAllField)
                    {
                        Command command = Configuration.Instance.GetRetrieveCommand(request.Entity.GetType());
                        if (command.ReturnEntities.Count > 0)
                        {
                            ReturnEntity returnEntity = command.ReturnEntities[0];
                            if (returnEntity.EntityReturnMode == EntityReturnMode.Single)
                            {
                                sqlCommand = new SqlCommand(command.CommandName, connection);

                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                SetCommandParameters(command, sqlCommand);
                                SetParameterValues(command, sqlCommand, request);

                                IDataReader reader = sqlCommand.ExecuteReader();

                                if (reader.Read())
                                {
                                    entity = PopulateEntity(returnEntity.EntityType, reader, returnEntity);
                                }

                                reader.Close();
                                CloseConnection(connection);
                            }
                        }
                    }
                    else
                    {
                        string sql = "SELECT {0} FROM {1} WHERE EntityId = {2}";
                        string selectStr = request.GetReturnFieldString();
                        sqlCommand = connection.CreateCommand();
                        sqlCommand.CommandText = string.Format(sql, selectStr, Configuration.Instance.GetTableName(request.Entity.GetType()), request.EntityId);

                        IDataReader reader = sqlCommand.ExecuteReader();
                        if (reader.Read())
                        {
                            entity = PopulateEntity(request.Entity.GetType(), reader, selectStr);
                        }

                        reader.Close();
                        CloseConnection(connection);
                    }
                }

                return entity;
            }

        }
        public override EntityList GetEntityList(BaseRequestBinder requestBinder)
        {
            EntityList entityList = null;
            EntityListRequest request = requestBinder.Request as EntityListRequest;

            if (request != null && !string.IsNullOrEmpty(request.CommandIdent))
            {
                using (SqlConnection connection = GetConnection())
                {
                    Command command = Configuration.Instance.GetCommand(request.CommandIdent);
                    SqlCommand sqlCommand = GetSqlCommand(connection, command, request);

                    IDataReader reader = sqlCommand.ExecuteReader();

                    EntityList currentEntityList = null;
                    EntityList childEntityList = new EntityList();
                    foreach (ReturnEntity returnEntity in command.ReturnEntities)
                    {
                        if (returnEntity.EntityReturnMode == EntityReturnMode.Single)
                        {
                            if (reader.Read())
                            {
                                childEntityList.Add(PopulateEntity(returnEntity.EntityType, reader, returnEntity));
                            }
                        }
                        else if (returnEntity.EntityReturnMode == EntityReturnMode.Multiple)
                        {
                            currentEntityList = new EntityList();
                            while (reader.Read())
                            {
                                currentEntityList.Add(PopulateEntity(returnEntity.EntityType, reader, returnEntity));
                            }
                            if (request.Entity.GetType().IsAssignableFrom(returnEntity.EntityType))
                            {
                                entityList = currentEntityList;
                            }
                            else
                            {
                                childEntityList.Add(currentEntityList);
                            }
                        }
                        reader.NextResult();
                    }

                    if (entityList != null)
                    {
                        entityList.EntityList = childEntityList;
                    }

                    SetOutputParameterValues(command, sqlCommand, (EntityReply)requestBinder.Reply);

                    reader.Close();
                    CloseConnection(connection);

                    return entityList;
                }
            }
            else
            {
                string selectSQL = @"
                    SET NOCOUNT ON

                    CREATE TABLE #t
                    (
                        IndexId INT IDENTITY (1, 1) NOT NULL,
                        EntityId INT
                    )
                    CREATE TABLE #EntityIdTable
                    (
                        EntityId INT
                    )

                    --Get total records count
                    DECLARE @sql NVARCHAR(4000)

                    SET @sql= N'SELECT @TotalRecords = COUNT(*) FROM ({0}) a'
                    EXECUTE sp_executesql @sql, N'{2}@TotalRecords INT OUTPUT', {3}@TotalRecords OUTPUT

                    -- Get the record keys of the current page
                    SET @sql = N'INSERT INTO #t(EntityId) SELECT TOP ' + CAST(@pageSize AS NVARCHAR(20)) + N' EntityId FROM ({1}) t ' + @orderSql
                    EXECUTE sp_executesql @sql{4}
                    INSERT INTO #EntityIdTable SELECT EntityId FROM #t ORDER BY IndexId DESC

                    -- Get the record entitys of the current page
                    SET @sql = N'SELECT ' + @ReturnFields + N' FROM #EntityIdTable dt INNER JOIN  [' + @TableName + N'] t ON dt.EntityId = t.EntityId'
                    EXECUTE sp_executesql @sql

                    DROP TABLE #t
                    DROP TABLE #EntityIdTable

                    SET NOCOUNT OFF
                    ";

                string tableName = request.GetTableName();
                string returnFields = request.GetReturnFieldString();
                int pageSize = request.PageSize;
                string orderSQL = GetDisOrderSql(request);
                ICondition condition = GetCondition(request);
                string conditionExpression = null;
                if (condition != null && condition.IsValid(request))
                {
                    conditionExpression = condition.GetConditionExpression(request);
                }
                string sqlFullPopulate = GetPopulateCountSql(request, conditionExpression);
                string sqlPopulate = GetPopulateSql(request, conditionExpression);
                string parametersFormat1 = string.Empty;
                string parametersFormat2 = string.Empty;
                string parametersFormat3 = string.Empty;

                using (SqlConnection connection = GetConnection())
                {
                    SqlCommand sqlCommand = connection.CreateCommand();

                    AddParameter(sqlCommand, "@TableName", tableName, SqlDbType.NVarChar, 50);
                    AddParameter(sqlCommand, "@ReturnFields", returnFields, SqlDbType.NVarChar, 4000);
                    AddParameter(sqlCommand, "@pageSize", pageSize, SqlDbType.Int, 4);
                    AddParameter(sqlCommand, "@orderSql", orderSQL, SqlDbType.NVarChar, 256);
                    AddOutputParameter(sqlCommand, "@TotalRecords", SqlDbType.Int, 4);

                    ProcessConditions(request, condition, sqlCommand, ref parametersFormat1, ref parametersFormat2, ref parametersFormat3);
                    sqlCommand.CommandText = string.Format(selectSQL, sqlFullPopulate, sqlPopulate, parametersFormat1, parametersFormat2, parametersFormat3);

                    IDataReader reader = sqlCommand.ExecuteReader();

                    entityList = new EntityList();
                    Type type = request.Entity.GetType();

                    List<string> fieldNameList = new List<string>();

                    foreach (string fieldName in returnFields.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries))
                    {
                        fieldNameList.Add(fieldName.Substring(2));
                    }

                    while (reader.Read())
                    {
                        Entity entity = PopulateEntityFromIDataReader(type, reader, fieldNameList);
                        if (entity != null)
                        {
                            entityList.Add(entity);
                        }
                    }

                    reader.Close();
                    CloseConnection(connection);

                    entityList.TotalCount = (int)sqlCommand.Parameters["@TotalRecords"].Value;

                    if (request.PageIndex < 0 || request.PageIndex + 1 > CalculateTotalPages(entityList.TotalCount, request.PageSize))
                    {
                        return new EntityList();
                    }

                    if (entityList.TotalCount > request.PageSize)
                    {
                        if ((request.PageIndex.Value + 1) * request.PageSize > entityList.TotalCount && request.PageIndex.Value * request.PageSize < entityList.TotalCount)
                        {
                            EntityList modifiedEntityList = new EntityList();
                            for (int i = (request.PageIndex.Value + 1) * request.PageSize - entityList.TotalCount; i < entityList.Count; i++)
                            {
                                modifiedEntityList.Add(entityList[i]);
                            }
                            modifiedEntityList.TotalCount = entityList.TotalCount;
                            return modifiedEntityList;
                        }
                        return entityList;
                    }
                    else
                    {
                        return entityList;
                    }
                }
            }
        }
        public override SqlResult CreateEntity(BaseRequestBinder requestBinder)
        {
            return CreateEntity(GetConnection(), requestBinder);
        }
        public override SqlResult UpdateEntity(BaseRequestBinder requestBinder)
        {
            return UpdateEntity(GetConnection(), requestBinder);
        }
        public override SqlResult DeleteEntity(BaseRequestBinder requestBinder)
        {
            return DeleteEntity(GetConnection(), requestBinder);
        }
        public override SqlResult ExecuteTransaction(List<BaseRequestBinder> requestBinders)
        {
            SqlResult result = new SqlResult();
            SqlConnection connection = GetConnection();
            SqlTransaction transaction = connection.BeginTransaction();
            EntityRequest entityRequest;
            BaseRequestBinder requestBinder = null;

            try
            {
                foreach (BaseRequestBinder binder in requestBinders)
                {
                    requestBinder = binder;
                    entityRequest = requestBinder.Request as EntityRequest;

                    if (entityRequest.Operation == OperationType.Add)
                    {
                        UpdateRequestDependencies(entityRequest);
                        result = CreateEntity(connection, transaction, binder);
                        if (result.Status != 0)
                        {
                            transaction.Rollback();
                            break;
                        }
                        requestBinder.Reply.Status = result.Status;
                        requestBinder.Reply.Log = result.Message;
                    }
                    else if (entityRequest.Operation == OperationType.Update)
                    {
                        UpdateRequestDependencies(entityRequest);
                        result = UpdateEntity(connection, transaction, binder);
                        if (result.Status != 0)
                        {
                            transaction.Rollback();
                            break;
                        }
                        requestBinder.Reply.Status = result.Status;
                        requestBinder.Reply.Log = result.Message;
                    }
                    else if (entityRequest.Operation == OperationType.Delete)
                    {
                        UpdateRequestDependencies(entityRequest);
                        result = DeleteEntity(connection, transaction, binder);
                        if (result.Status != 0)
                        {
                            transaction.Rollback();
                            break;
                        }
                        requestBinder.Reply.Status = result.Status;
                        requestBinder.Reply.Log = result.Message;
                    }
                    else
                    {
                        result = new SqlResult();
                        requestBinder.Reply.Status = result.Status;
                        transaction.Rollback();
                        break;
                    }
                }
                transaction.Commit();
            }
            catch (Exception ex)
            {
                result = new SqlResult();
                result.Message = ex.Message;
                if (requestBinder != null)
                {
                    requestBinder.Reply.Status = result.Status;
                    requestBinder.Reply.Log = result.Message;
                }
                transaction.Rollback();
            }
            finally
            {
                transaction.Dispose();
                transaction = null;
                CloseConnection(connection);
            }

            return result;
        }

        #endregion

        #region Private Methods

        private SqlConnection GetConnection()
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            return connection;
        }
        private void CloseConnection(SqlConnection connection)
        {
            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
                connection = null;
            }
        }

        private SqlResult ExecuteRequest(SqlConnection connection, SqlTransaction transaction, Command command, BaseRequestBinder requestBinder, object commandData)
        {
            SqlResult result = new SqlResult();

            try
            {
                SqlCommand sqlCommand = new SqlCommand(command.CommandName, connection);

                sqlCommand.Transaction = transaction;
                sqlCommand.CommandType = CommandType.StoredProcedure;

                SetCommandParameters(command, sqlCommand);

                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@ReturnValue";
                parameter.SqlDbType = SqlDbType.Int;
                parameter.Size = 4;
                parameter.Direction = ParameterDirection.ReturnValue;
                sqlCommand.Parameters.Add(parameter);

                if (commandData is Entity)
                {
                    SetParameterValues(command, sqlCommand, (Entity)commandData);
                }
                else if (commandData is EntityRequest)
                {
                    SetParameterValues(command, sqlCommand, (EntityRequest)commandData);
                }
                else
                {
                    throw new Exception("Command Data invalid type.");
                }

                sqlCommand.ExecuteNonQuery();

                if (commandData is Entity)
                {
                    SetOutputParameterValues(command, sqlCommand, (Entity)commandData);
                }
                else if (commandData is EntityRequest)
                {
                    SetOutputParameterValues(command, sqlCommand, (EntityReply)requestBinder.Reply);
                }

                if (parameter.Value != null && parameter.Value != DBNull.Value)
                {
                    result.Status = (int)parameter.Value;
                }
            }
            catch (Exception ex)
            {
                result = new SqlResult();
                result.Message = ex.Message;
            }
            finally
            {
                if (transaction == null)
                {
                    CloseConnection(connection);
                }
            }

            return result;
        }
        private SqlResult Update(SqlConnection connection, SqlTransaction transaction, Entity entity)
        {
            SqlResult result = new SqlResult();

            try
            {
                Hashtable parameterList = new Hashtable();

                SqlCommand sqlCommand = connection.CreateCommand();

                sqlCommand.Transaction = transaction;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = GetUpdateSQL(entity, parameterList);

                AssignParameterValues(sqlCommand, parameterList);

                SqlParameter parameter = new SqlParameter();
                parameter.Direction = ParameterDirection.ReturnValue;
                sqlCommand.Parameters.Add(parameter);

                sqlCommand.ExecuteNonQuery();

                if (parameter.Value != null && parameter.Value != DBNull.Value)
                {
                    result.Status = (int)parameter.Value;
                }
            }
            catch (Exception ex)
            {
                result = new SqlResult();
                result.Message = ex.Message;
            }
            finally
            {
                if (transaction == null)
                {
                    CloseConnection(connection);
                }
            }

            return result;
        }
        private SqlResult CreateEntity(SqlConnection connection, BaseRequestBinder requestBinder)
        {
            return CreateEntity(connection, null, requestBinder);
        }
        private SqlResult UpdateEntity(SqlConnection connection, BaseRequestBinder requestBinder)
        {
            return UpdateEntity(connection, null, requestBinder);
        }
        private SqlResult DeleteEntity(SqlConnection connection, BaseRequestBinder requestBinder)
        {
            return DeleteEntity(connection, null, requestBinder);
        }
        private SqlResult CreateEntity(SqlConnection connection, SqlTransaction transaction, BaseRequestBinder requestBinder)
        {
            SqlResult result = new SqlResult();

            EntityRequest request = requestBinder.Request as EntityRequest;

            if (request != null)
            {
                if (!string.IsNullOrEmpty(request.CommandIdent))
                {
                    result = ExecuteRequest(connection, transaction, Configuration.Instance.GetCommand(request.CommandIdent), requestBinder, request);
                }
                else if (request.Entity != null)
                {
                    result = ExecuteRequest(connection, transaction, Configuration.Instance.GetCreateCommand(request.Entity.GetType()), requestBinder, request.Entity);
                }
            }
            return result;
        }
        private SqlResult UpdateEntity(SqlConnection connection, SqlTransaction transaction, BaseRequestBinder requestBinder)
        {
            SqlResult result = new SqlResult();

            EntityRequest request = requestBinder.Request as EntityRequest;

            if (request != null)
            {
                if (!request.SaveModifiedProperties)
                {
                    if (!string.IsNullOrEmpty(request.CommandIdent))
                    {
                        result = ExecuteRequest(connection, transaction, Configuration.Instance.GetCommand(request.CommandIdent), requestBinder, request);
                    }
                    else if (request.Entity != null)
                    {
                        result = ExecuteRequest(connection, transaction, Configuration.Instance.GetUpdateCommand(request.Entity.GetType()), requestBinder, request.Entity);
                    }
                }
                else
                {
                    result = Update(connection, transaction, request.Entity);
                }
            }
            return result;
        }
        private SqlResult DeleteEntity(SqlConnection connection, SqlTransaction transaction, BaseRequestBinder requestBinder)
        {
            SqlResult result = new SqlResult();

            EntityRequest request = requestBinder.Request as EntityRequest;

            if (request != null)
            {
                if (!string.IsNullOrEmpty(request.CommandIdent))
                {
                    result = ExecuteRequest(connection, transaction, Configuration.Instance.GetCommand(request.CommandIdent), requestBinder, request);
                }
                else if (request.Entity != null)
                {
                    result = ExecuteRequest(connection, transaction, Configuration.Instance.GetDeleteCommand(request.Entity.GetType()), requestBinder, request);
                }
            }
            return result;
        }

        private ICondition GetCondition(EntityRequest request)
        {
            foreach (DictionaryEntry entry in Configuration.Instance.EntityRequests)
            {
                if (((Type)entry.Key).FullName == request.GetType().FullName)
                {
                    return entry.Value as ICondition;
                }
            }
            return null;
        }
        private string GetSelectTopSql(EntityListRequest request)
        {
            string selectStr = "t.EntityId";

            StringBuilder sb = new StringBuilder();

            if (request.OrderFields.Count == 0)
            {
                request.OrderFields.AddField(new OrderField(request.Entity, "EntityId", false));
            }
            foreach (OrderField field in request.OrderFields)
            {
                if (field.FieldName == "EntityId")
                {
                    continue;
                }
                sb.AppendFormat(", {0}", string.Format("t.{0}", field.FieldName));
            }
            selectStr += sb.ToString();

            return selectStr;
        }
        private string GetSelectSql(EntityListRequest request)
        {
            return string.Format("SELECT TOP {1} {2} FROM {0} t WHERE 1 = 1", request.GetTableName(), (request.PageIndex + 1) * request.PageSize, GetSelectTopSql(request));
        }
        private string GetSelectCountSql(EntityListRequest request)
        {
            return "SELECT t.EntityId FROM " + request.GetTableName() + " t WHERE 1 = 1";
        }
        private string GetOrderSql(EntityListRequest request)
        {
            List<string> orderItemList = new List<string>();

            if (request.OrderFields.Count == 0)
            {
                request.OrderFields.AddField(new OrderField(request.Entity, "EntityId", false));
            }
            foreach (OrderField field in request.OrderFields)
            {
                orderItemList.Add(string.Format("{0} {1}", string.Format("t.{0}", field.FieldName), field.IsAscending ? "ASC" : "DESC"));
            }

            return string.Format(" ORDER BY {0} ", string.Join(",", orderItemList.ToArray()));
        }
        private string GetDisOrderSql(EntityListRequest request)
        {
            List<string> disOrderItemList = new List<string>();

            if (request.OrderFields.Count == 0)
            {
                request.OrderFields.AddField(new OrderField(request.Entity, "EntityId", false));
            }
            foreach (OrderField field in request.OrderFields)
            {
                disOrderItemList.Add(string.Format("{0} {1}", field.FieldName, !field.IsAscending ? "ASC" : "DESC"));
            }

            return string.Format("ORDER BY {0}", string.Join(",", disOrderItemList.ToArray()));
        }
        private string GetPopulateSql(EntityListRequest request, string conditionSql)
        {
            string selectSql = GetSelectSql(request);
            string orderSql = GetOrderSql(request);

            if (!string.IsNullOrEmpty(conditionSql))
            {
                return selectSql + string.Format(" AND{0}", conditionSql) + orderSql;
            }
            return selectSql + orderSql;
        }
        private string GetPopulateCountSql(EntityListRequest request, string conditionSql)
        {
            string selectCountSql = GetSelectCountSql(request);

            if (!string.IsNullOrEmpty(conditionSql))
            {
                return selectCountSql + string.Format(" AND{0}", conditionSql);
            }
            return selectCountSql;
        }

        private void UpdateRequestDependencies(EntityRequest request)
        {
            foreach (RequestDependency dependency in request.RequestDependencies)
            {
                PropertyInfo targetProperty = dependency.TargetObject.GetType().GetProperty(dependency.TargetPropertyName);
                PropertyInfo sourceProperty = dependency.SourceObject.GetType().GetProperty(dependency.SourcePropertyName);
                object sourceValue = sourceProperty.GetValue(dependency.SourceObject, null);
                Property targetValue = Activator.CreateInstance(targetProperty.PropertyType) as Property;

                if (sourceValue != null && typeof(Property).IsAssignableFrom(sourceValue.GetType()))
                {
                    sourceValue = ((Property)sourceValue).ObjectValue;
                }
                if (targetValue != null)
                {
                    targetValue.ObjectValue = sourceValue;
                    targetProperty.SetValue(dependency.TargetObject, targetValue, null);
                }
                else
                {
                    targetProperty.SetValue(dependency.TargetObject, sourceValue, null);
                }
            }
        }
        private static Entity PopulateEntityFromIDataReader(Type entityClassType, IDataReader reader, List<string> fieldNameList)
        {
            if (entityClassType == null)
            {
                return null;
            }

            Entity entity = Activator.CreateInstance(entityClassType) as Entity;

            PropertyInfo propertyInfo = null;
            Property property = null;
            object propertyValue = null;

            foreach (string fieldName in fieldNameList)
            {
                propertyInfo = entityClassType.GetProperty(Configuration.Instance.GetPropertyName(entity.GetType(), fieldName));
                property = Activator.CreateInstance(propertyInfo.PropertyType) as Property;
                propertyValue = reader[fieldName];
                if (propertyValue != DBNull.Value)
                {
                    property.ObjectValue = propertyValue;
                }
                propertyInfo.SetValue(entity, property, null);
            }

            return entity;
        }
        private SqlCommand GetSqlCommand(SqlConnection connection, Command command, EntityRequest entityRequest)
        {
            SqlCommand sqlCommand = new SqlCommand(command.CommandName, connection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            SetCommandParameters(command, sqlCommand);
            SetParameterValues(command, sqlCommand, entityRequest);

            return sqlCommand;
        }
        private static void SetCommandParameters(Command command, SqlCommand sqlCommand)
        {
            if (sqlCommand == null || command == null)
            {
                return;
            }

            SqlParameterCollection parameters = sqlCommand.Parameters;
            SqlParameter parameter;

            foreach (CommandParameter commandParameter in command.Parameters.Values)
            {
                parameter = new SqlParameter();

                parameter.ParameterName = commandParameter.ParameterName;
                parameter.Direction = commandParameter.RealParameterDirection;
                parameter.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), commandParameter.DbTypeHint);

                sqlCommand.Parameters.Add(parameter);
            }
        }
        private static void SetOutputParameterValues(Command command, SqlCommand sqlCommand, Entity entity)
        {
            SqlParameter sqlParameter;
            PropertyInfo propertyInfo;
            Property property;

            foreach (CommandParameter commandParameter in command.Parameters.Values)
            {
                if (commandParameter.RealParameterDirection == ParameterDirection.Output)
                {
                    sqlParameter = sqlCommand.Parameters[commandParameter.ParameterName];
                    if (sqlParameter.Value != DBNull.Value)
                    {
                        propertyInfo = entity.GetType().GetProperty(commandParameter.ClassMember);
                        property = Activator.CreateInstance(propertyInfo.PropertyType) as Property;
                        property.ObjectValue = sqlParameter.Value;
                        propertyInfo.SetValue(entity, property, null);
                    }
                }
            }
        }
        private static void SetOutputParameterValues(Command command, SqlCommand sqlCommand, EntityReply entityReply)
        {
            SqlParameter sqlParameter;

            foreach (CommandParameter commandParameter in command.Parameters.Values)
            {
                if (commandParameter.RealParameterDirection == ParameterDirection.Output)
                {
                    sqlParameter = sqlCommand.Parameters[commandParameter.ParameterName];
                    if (sqlParameter.Value != DBNull.Value)
                    {
                        entityReply.GetType().GetProperty(commandParameter.ClassMember).SetValue(entityReply, sqlParameter.Value, null);
                    }
                }
            }
        }
        private void SetParameterValues(Command command, SqlCommand sqlCommand, EntityRequest entityRequest)
        {
            object parameterValue = null;
            foreach (CommandParameter commandParameter in command.Parameters.Values)
            {
                if (commandParameter.RealParameterDirection == ParameterDirection.Input)
                {
                    parameterValue = entityRequest.GetType().GetProperty(commandParameter.ClassMember).GetValue(entityRequest, null);
                    if (parameterValue == null)
                    {
                        sqlCommand.Parameters[commandParameter.ParameterName].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters[commandParameter.ParameterName].Value = parameterValue;
                    }
                }
            }
        }
        private void SetParameterValues(Command command, SqlCommand sqlCommand, Entity entity)
        {
            object parameterValue = null;
            foreach (CommandParameter commandParameter in command.Parameters.Values)
            {
                if (commandParameter.RealParameterDirection == ParameterDirection.Input)
                {
                    parameterValue = ((Property)entity.GetType().GetProperty(commandParameter.ClassMember).GetValue(entity, null)).ObjectValue;
                    if (parameterValue == null)
                    {
                        sqlCommand.Parameters[commandParameter.ParameterName].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters[commandParameter.ParameterName].Value = parameterValue;
                    }
                }
            }
        }
        private void ProcessConditions(EntityRequest request, ICondition condition, SqlCommand sqlCommand, ref string parametersFormat1, ref string parametersFormat2, ref string parametersFormat3)
        {
            if (request == null || condition == null)
            {
                return;
            }

            List<string> formatItemList1 = new List<string>();
            List<string> formatItemList2 = new List<string>();
            List<string> formatItemList3 = new List<string>();
            List<string> formatItemList4 = new List<string>();
            string format = ", N'{0}', {1}";

            ProcessCondition(request, condition, sqlCommand, ref formatItemList1, ref formatItemList2, ref formatItemList3, ref formatItemList4);

            parametersFormat1 = string.Join("", formatItemList1.ToArray());
            parametersFormat2 = string.Join("", formatItemList2.ToArray());
            if (formatItemList4.Count > 0)
            {
                parametersFormat3 = string.Format(format, string.Join(",", formatItemList3.ToArray()), string.Join(",", formatItemList4.ToArray()));
            }
        }
        private void ProcessCondition(EntityRequest request, ICondition condition, SqlCommand sqlCommand, ref List<string> formatItemList1, ref List<string> formatItemList2, ref List<string> formatItemList3, ref List<string> formatItemList4)
        {
            if (condition is IFieldCondition)
            {
                if (condition.IsValid(request))
                {
                    string format1 = "{0} {1}, ";
                    string format2 = "{0}, ";
                    string format3 = "{0} {1}";
                    string format4 = "{0}";
                    IFieldCondition fieldCondition = (IFieldCondition)condition;
                    FieldMapping fieldMapping = Configuration.Instance.GetFieldMappingByFieldName(request.Entity.GetType(), fieldCondition.DbFieldName);
                    string parameterName = fieldCondition.ParameterName;
                    formatItemList1.Add(string.Format(format1, parameterName, fieldMapping.FieldDBType));
                    formatItemList2.Add(string.Format(format2, parameterName));
                    formatItemList3.Add(string.Format(format3, parameterName, fieldMapping.FieldDBType));
                    formatItemList4.Add(string.Format(format4, parameterName));
                    AddParameter(sqlCommand, parameterName, fieldCondition.GetConditionValue(request), (SqlDbType)Enum.Parse(typeof(SqlDbType), fieldMapping.FieldDBTypeEnum), fieldMapping.FieldSize);
                }
            }
            else
            {
                foreach (ICondition childCondition in condition.ChildConditions)
                {
                    if (childCondition.IsValid(request))
                    {
                        ProcessCondition(request, childCondition, sqlCommand, ref formatItemList1, ref formatItemList2, ref formatItemList3, ref formatItemList4);
                    }
                }
            }
        }
        private Entity PopulateEntity(Type type, IDataReader reader, string selectStr)
        {
            Entity entity = Activator.CreateInstance(type) as Entity;
            string propertyName = null;
            Property property = null;
            object propertyValue = null;
            PropertyInfo propertyInfo = null;

            foreach (string fieldName in selectStr.Split(','))
            {
                propertyName = Configuration.Instance.GetPropertyName(type, fieldName);
                propertyInfo = type.GetProperty(propertyName);
                property = Activator.CreateInstance(propertyInfo.PropertyType) as Property;
                propertyValue = reader[fieldName];
                if (propertyValue != DBNull.Value)
                {
                    property.ObjectValue = propertyValue;
                }
                propertyInfo.SetValue(entity, property, null);
            }
            return entity;
        }
        private Entity PopulateEntity(Type type, IDataReader reader, ReturnEntity returnEntity)
        {
            Entity entity = Activator.CreateInstance(type) as Entity;
            Property property = null;
            object propertyValue = null;
            PropertyInfo propertyInfo = null;

            foreach (ReturnField returnField in returnEntity.ReturnFieldList)
            {
                propertyInfo = type.GetProperty(returnField.PropertyName);
                property = Activator.CreateInstance(propertyInfo.PropertyType) as Property;
                propertyValue = reader[returnField.FieldName];
                if (propertyValue != DBNull.Value)
                {
                    property.ObjectValue = propertyValue;
                }
                propertyInfo.SetValue(entity, property, null);
            }
            return entity;
        }
        private string GetUpdateSQL(Entity entity, Hashtable parameterList)
        {
            string sql = "UPDATE {0} SET {1} WHERE EntityId = {2}; RETURN @@ROWCOUNT";
            string setFormat = "{0} = @{0}";
            List<string> setList = new List<string>();
            int entityId = 0;
            string fieldName = null;
            Property propertyValue = null;

            foreach (PropertyInfo propertyInfo in entity.GetType().GetProperties())
            {
                if (propertyInfo.Name == "EntityId")
                {
                    entityId = Globals.ChangeType<int>(propertyInfo.GetValue(entity, null));
                }
                else
                {
                    fieldName = entity.GetDBFieldName(propertyInfo.Name);
                    propertyValue = propertyInfo.GetValue(entity, null) as Property;
                    if (propertyValue != null && propertyValue.IsDirty && propertyValue.ObjectValue != null)
                    {
                        setList.Add(string.Format(setFormat, fieldName));
                        parameterList.Add(@"@" + fieldName, propertyValue);
                    }
                }
            }

            return string.Format(sql, Configuration.Instance.GetTableName(entity.GetType()), string.Join(", ", setList.ToArray()), entityId);
        }
        private void AssignParameterValues(SqlCommand sqlCommand, Hashtable parameterList)
        {
            SqlParameter parameter = null;

            foreach (DictionaryEntry entry in parameterList)
            {
                parameter = new SqlParameter(entry.Key as string, entry.Value);
                parameter.Direction = ParameterDirection.Input;
                sqlCommand.Parameters.Add(parameter);
            }
        }
        private static int CalculateTotalPages(int totalRecords, int pageSize)
        {
            int totalPagesAvailable;

            if (totalRecords == 0)
                return 0;

            // First calculate the division
            //
            totalPagesAvailable = totalRecords / pageSize;

            // Now do a mod for any remainder
            //
            if ((totalRecords % pageSize) > 0)
                totalPagesAvailable++;

            return totalPagesAvailable;

        }
        private void AddParameter(SqlCommand command, string parameterName, object value, SqlDbType dbType, int size)
        {
            SqlParameter parameter = command.Parameters.Add(parameterName, dbType);
            if (value == null)
            {
                parameter.Value = DBNull.Value;
            }
            else
            {
                parameter.Value = value;
            }
            parameter.Size = size;
        }
        private void AddOutputParameter(SqlCommand command, string parameterName, SqlDbType dbType, int size)
        {
            SqlParameter parameter = command.Parameters.Add(parameterName, dbType);
            parameter.Direction = ParameterDirection.Output;
            parameter.Size = size;
        }

        #endregion
    }
}